﻿Imports System.Data.SqlClient
Imports System.Configuration

Public Class CopyTask
    Dim dsJob As DataSet = New DataSet("dsJob")
    Dim dtJobList As DataTable = New DataTable("dtJobList")
    Dim dtJobHistory As DataTable = New DataTable("dtJobHistory")
    Dim dtBudgetDetail As DataTable = New DataTable("dtBudgetDetail")
    Dim dtBudget12 As DataTable = New DataTable("dtBudget12")
    Dim dtFundRelease As DataTable = New DataTable("dtFundRelease")
    Dim dtAttachment As DataTable = New DataTable("dtAttachment")
    Dim dtBudgetRevPlans As DataTable = New DataTable("dtBudgetRevPlans")

    Dim OpenFlag As Boolean = False
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("WebBBConnectionString").ConnectionString
    Dim DB_Connect As New SqlConnection()
    Dim JobTransaction As SqlTransaction
    Dim Transcmd As SqlCommand
    Dim Transadap As SqlDataAdapter
    Dim Transreader As SqlDataReader
    Public Sub Copy(ByVal _copyname As String)
        'create DataSet for store DataTable
        createDS()
        'Get JobList by JobName
        GetJobList(_copyname)

        If dtJobList.Rows.Count > 0 Then
            For Each drJobList As DataRow In dtJobList.Rows
                ExecuteTransaction(drJobList)
            Next
        End If
    End Sub
    Private Sub createDS()
        dsJob = New DataSet("dsJob")
        'dtJobList = New DataTable("dtJobList")
        'dtJobHistory = New DataTable("dtJobHistory")
        'dtBudgetDetail = New DataTable("dtBudgetDetail")
        '******************************************************************************************
        'DataTable JobList
        dtJobList.Columns.Add("JobId", GetType(Integer))
        dtJobList.Columns.Add("JobName", GetType(String))
        dtJobList.Columns.Add("JobStatus", GetType(String))
        dtJobList.Columns.Add("SubmitBy", GetType(String))
        dtJobList.Columns.Add("Remark", GetType(String))
        dtJobList.Columns.Add("FundCenterCode", GetType(String))
        dtJobList.Columns.Add("PlanYear", GetType(Integer))
        dtJobList.Columns.Add("FromVersion", GetType(String))
        dtJobList.Columns.Add("ToVersion", GetType(String))
        dtJobList.Columns.Add("CreateDate", GetType(Date))
        dtJobList.Columns.Add("CreateBy", GetType(String))
        dtJobList.Columns.Add("UpdateDate", GetType(Date))
        dtJobList.Columns.Add("UpdateBy", GetType(String))
        dsJob.Tables.Add(dtJobList)
        '******************************************************************************************
        'DataTable JobHistory
        dtJobHistory.Columns.Add("Id", GetType(Integer))
        dtJobHistory.Columns.Add("JobId", GetType(Integer))
        dtJobHistory.Columns.Add("StartTime", GetType(Date))
        dtJobHistory.Columns.Add("EndTime", GetType(Date))
        dsJob.Tables.Add(dtJobHistory)
        '******************************************************************************************
        'DataTable BudgetDetail
        dtBudgetDetail.Columns.Add("Id", GetType(Integer)) '1
        dtBudgetDetail.Columns.Add("PlanYear", GetType(Integer)) '2
        dtBudgetDetail.Columns.Add("BaseYear", GetType(Integer)) '3
        dtBudgetDetail.Columns.Add("Version", GetType(String)) '4
        dtBudgetDetail.Columns.Add("AssetNo", GetType(String)) '5
        dtBudgetDetail.Columns.Add("AssetDescription", GetType(String)) '6
        dtBudgetDetail.Columns.Add("AssetRowNo", GetType(Integer)) '7
        dtBudgetDetail.Columns.Add("OldAssetNo", GetType(String)) '8
        dtBudgetDetail.Columns.Add("Element1", GetType(Decimal)) '9
        dtBudgetDetail.Columns.Add("Element2", GetType(Decimal)) '10
        dtBudgetDetail.Columns.Add("Element3", GetType(Decimal)) '11
        dtBudgetDetail.Columns.Add("Element4", GetType(Decimal)) '12
        dtBudgetDetail.Columns.Add("Element5", GetType(Decimal)) '13
        dtBudgetDetail.Columns.Add("Element6", GetType(Decimal)) '14
        dtBudgetDetail.Columns.Add("Element7", GetType(Decimal)) '15
        dtBudgetDetail.Columns.Add("Element8", GetType(Decimal)) '16
        dtBudgetDetail.Columns.Add("Element9", GetType(Decimal)) '17
        dtBudgetDetail.Columns.Add("Amount", GetType(Decimal)) '18
        dtBudgetDetail.Columns.Add("NewQuantity", GetType(Decimal)) '19
        dtBudgetDetail.Columns.Add("ReplaceQuantity", GetType(Decimal)) '20
        dtBudgetDetail.Columns.Add("PricePerUnit", GetType(Decimal)) '21
        dtBudgetDetail.Columns.Add("Note", GetType(String)) '22
        dtBudgetDetail.Columns.Add("Identification", GetType(String)) '23
        dtBudgetDetail.Columns.Add("FormId", GetType(String)) '24
        dtBudgetDetail.Columns.Add("FndPrgGenFlag", GetType(String)) '25
        dtBudgetDetail.Columns.Add("FundedProgram", GetType(String)) '26
        dtBudgetDetail.Columns.Add("TmpFundedProgram", GetType(String)) '27
        dtBudgetDetail.Columns.Add("FunctionalArea", GetType(String)) '28
        dtBudgetDetail.Columns.Add("Actual1", GetType(Decimal)) '29
        dtBudgetDetail.Columns.Add("Actual2", GetType(Decimal)) '30
        dtBudgetDetail.Columns.Add("Actual3", GetType(Decimal)) '31
        dtBudgetDetail.Columns.Add("Actual4", GetType(Decimal)) '32
        dtBudgetDetail.Columns.Add("Actual5", GetType(Decimal)) '33
        dtBudgetDetail.Columns.Add("Actual6", GetType(Decimal)) '34
        dtBudgetDetail.Columns.Add("Actual7", GetType(Decimal)) '35
        dtBudgetDetail.Columns.Add("Actual8", GetType(Decimal)) '36
        dtBudgetDetail.Columns.Add("Actual9", GetType(Decimal)) '37
        dtBudgetDetail.Columns.Add("Actual10", GetType(Decimal)) '38
        dtBudgetDetail.Columns.Add("Actual11", GetType(Decimal)) '39
        dtBudgetDetail.Columns.Add("Actual12", GetType(Decimal)) '40
        dtBudgetDetail.Columns.Add("Actual13", GetType(Decimal)) '41
        dtBudgetDetail.Columns.Add("Actual14", GetType(Decimal)) '42
        dtBudgetDetail.Columns.Add("Actual15", GetType(Decimal)) '43
        dtBudgetDetail.Columns.Add("Actual16", GetType(Decimal)) '44
        dtBudgetDetail.Columns.Add("Attachments_amount", GetType(Integer)) '45
        dtBudgetDetail.Columns.Add("ProductItemNo", GetType(Integer)) '46
        dtBudgetDetail.Columns.Add("CreatedDate", GetType(String)) '47
        dtBudgetDetail.Columns.Add("UpdatedDate", GetType(String)) '48
        dtBudgetDetail.Columns.Add("CreatedBy", GetType(String)) '49
        dtBudgetDetail.Columns.Add("UpdatedBy", GetType(String)) '50
        dtBudgetDetail.Columns.Add("ActivityType_PlanYear", GetType(Integer)) '51
        dtBudgetDetail.Columns.Add("ActivityType_ActivityTypeCode", GetType(String)) '52
        dtBudgetDetail.Columns.Add("Activity_ActivityCode", GetType(String)) '53
        dtBudgetDetail.Columns.Add("Activity_PlanYear", GetType(Integer)) '54
        dtBudgetDetail.Columns.Add("Product_ProductCode", GetType(String)) '55
        dtBudgetDetail.Columns.Add("Product_PlanYear", GetType(Integer)) '56
        dtBudgetDetail.Columns.Add("FundCenter_FundCenterCode", GetType(String)) '57
        dtBudgetDetail.Columns.Add("FundCenter_PlanYear", GetType(Integer)) '58
        dtBudgetDetail.Columns.Add("Project_ProjectCode", GetType(String)) '59
        dtBudgetDetail.Columns.Add("Project_PlanYear", GetType(Integer)) '60
        dtBudgetDetail.Columns.Add("Project_FundCenterCode", GetType(String)) '61
        dtBudgetDetail.Columns.Add("CmmtDetail_Id", GetType(Integer)) '62
        dtBudgetDetail.Columns.Add("Fund_FundCode", GetType(String)) '63
        dtBudgetDetail.Columns.Add("Fund_PlanYear", GetType(Integer)) '64
        dtBudgetDetail.Columns.Add("Cmmt_CmmtCode", GetType(String)) '65
        dtBudgetDetail.Columns.Add("Cmmt_PlanYear", GetType(Integer)) '66
        dsJob.Tables.Add(dtBudgetDetail)
        '******************************************************************************************
        'Datatable Budget12
        dtBudget12.Columns.Add("Id", GetType(Integer)) '1
        dtBudget12.Columns.Add("PlanYear", GetType(Integer)) '2
        dtBudget12.Columns.Add("BaseYear", GetType(Integer)) '3
        dtBudget12.Columns.Add("Version", GetType(String)) '4
        dtBudget12.Columns.Add("FunctionalArea", GetType(String)) '5
        dtBudget12.Columns.Add("FundedProgram", GetType(String)) '6
        dtBudget12.Columns.Add("Amount1", GetType(Decimal)) '7
        dtBudget12.Columns.Add("Amount2", GetType(Decimal)) '8
        dtBudget12.Columns.Add("Amount3", GetType(Decimal)) '9
        dtBudget12.Columns.Add("Amount4", GetType(Decimal)) '10
        dtBudget12.Columns.Add("Amount5", GetType(Decimal)) '11
        dtBudget12.Columns.Add("Amount6", GetType(Decimal)) '12
        dtBudget12.Columns.Add("Amount7", GetType(Decimal)) '13
        dtBudget12.Columns.Add("Amount8", GetType(Decimal)) '14
        dtBudget12.Columns.Add("Amount9", GetType(Decimal)) '15
        dtBudget12.Columns.Add("Amount10", GetType(Decimal)) '16
        dtBudget12.Columns.Add("Amount11", GetType(Decimal)) '17
        dtBudget12.Columns.Add("Amount12", GetType(Decimal)) '18
        dtBudget12.Columns.Add("ForecastYear1", GetType(Decimal)) '19
        dtBudget12.Columns.Add("ForecastYear2", GetType(Decimal)) '20
        dtBudget12.Columns.Add("ForecastYear3", GetType(Decimal)) '21
        dtBudget12.Columns.Add("ForecastYear4", GetType(Decimal)) '22
        dtBudget12.Columns.Add("ProductItemNo", GetType(Integer)) '23
        dtBudget12.Columns.Add("CreatedDate", GetType(Date)) '24
        dtBudget12.Columns.Add("UpdatedDate", GetType(Date)) '25
        dtBudget12.Columns.Add("CreatedBy", GetType(String)) '26
        dtBudget12.Columns.Add("UpdatedBy", GetType(String)) '27
        dtBudget12.Columns.Add("FundCenter_FundCenterCode", GetType(String)) '28
        dtBudget12.Columns.Add("FundCenter_PlanYear", GetType(Integer)) '29
        dtBudget12.Columns.Add("Fund_FundCode", GetType(String)) '30
        dtBudget12.Columns.Add("Fund_PlanYear", GetType(Integer)) '31
        dtBudget12.Columns.Add("Cmmt_CmmtCode", GetType(String)) '32
        dtBudget12.Columns.Add("Cmmt_PlanYear", GetType(Integer)) '33
        dtBudget12.Columns.Add("CmmtDetail_Id", GetType(Integer)) '34
        dtBudget12.Columns.Add("ActivityType_PlanYear", GetType(Integer)) '35
        dtBudget12.Columns.Add("ActivityType_ActivityTypeCode", GetType(String)) '36
        dtBudget12.Columns.Add("Activity_ActivityCode", GetType(String)) '37
        dtBudget12.Columns.Add("Activity_PlanYear", GetType(Integer)) '38
        dtBudget12.Columns.Add("Product_ProductCode", GetType(String)) '39
        dtBudget12.Columns.Add("Product_PlanYear", GetType(Integer)) '40
        dtBudget12.Columns.Add("Project_ProjectCode", GetType(String)) '41
        dtBudget12.Columns.Add("Project_PlanYear", GetType(Integer)) '42
        dtBudget12.Columns.Add("Project_FundCenterCode", GetType(String)) '43
        dtBudget12.Columns.Add("BudgetDetail_Id", GetType(Integer)) '44
        dsJob.Tables.Add(dtBudget12)
        '******************************************************************************************
        'Datatable BudgetRevPlans
        dtBudgetRevPlans.Columns.Add("Id", GetType(Integer)) '1
        dtBudgetRevPlans.Columns.Add("PlanYear", GetType(Integer)) '2
        dtBudgetRevPlans.Columns.Add("BaseYear", GetType(Integer)) '3
        dtBudgetRevPlans.Columns.Add("Version", GetType(String)) '4
        dtBudgetRevPlans.Columns.Add("ActualLastYear2", GetType(Decimal)) '5
        dtBudgetRevPlans.Columns.Add("ActualLastYear1", GetType(Decimal)) '6
        dtBudgetRevPlans.Columns.Add("ForecastLastYear1", GetType(Decimal)) '7
        dtBudgetRevPlans.Columns.Add("ForecastCurYear", GetType(Decimal)) '8
        dtBudgetRevPlans.Columns.Add("LastYearMonth1", GetType(Decimal)) '9
        dtBudgetRevPlans.Columns.Add("LastYearMonth2", GetType(Decimal)) '10
        dtBudgetRevPlans.Columns.Add("LastYearMonth3", GetType(Decimal)) '11
        dtBudgetRevPlans.Columns.Add("LastYearMonth4", GetType(Decimal)) '12
        dtBudgetRevPlans.Columns.Add("LastYearMonth5", GetType(Decimal)) '13
        dtBudgetRevPlans.Columns.Add("LastYearMonth6", GetType(Decimal)) '14
        dtBudgetRevPlans.Columns.Add("LastYearMonth7", GetType(Decimal)) '15
        dtBudgetRevPlans.Columns.Add("LastYearMonth8", GetType(Decimal)) '16
        dtBudgetRevPlans.Columns.Add("LastYearMonth9", GetType(Decimal)) '17
        dtBudgetRevPlans.Columns.Add("LastYearMonth10", GetType(Decimal)) '18
        dtBudgetRevPlans.Columns.Add("LastYearMonth11", GetType(Decimal)) '19
        dtBudgetRevPlans.Columns.Add("LastYearMonth12", GetType(Decimal)) '20
        dtBudgetRevPlans.Columns.Add("CurYearMonth1", GetType(Decimal)) '21
        dtBudgetRevPlans.Columns.Add("CurYearMonth2", GetType(Decimal)) '22
        dtBudgetRevPlans.Columns.Add("CurYearMonth3", GetType(Decimal)) '23
        dtBudgetRevPlans.Columns.Add("CurYearMonth4", GetType(Decimal)) '24
        dtBudgetRevPlans.Columns.Add("CurYearMonth5", GetType(Decimal)) '25
        dtBudgetRevPlans.Columns.Add("CurYearMonth6", GetType(Decimal)) '26
        dtBudgetRevPlans.Columns.Add("CurYearMonth7", GetType(Decimal)) '27
        dtBudgetRevPlans.Columns.Add("CurYearMonth8", GetType(Decimal)) '28
        dtBudgetRevPlans.Columns.Add("CurYearMonth9", GetType(Decimal)) '29
        dtBudgetRevPlans.Columns.Add("CurYearMonth10", GetType(Decimal)) '30
        dtBudgetRevPlans.Columns.Add("CurYearMonth11", GetType(Decimal)) '31
        dtBudgetRevPlans.Columns.Add("CurYearMonth12", GetType(Decimal)) '32
        dtBudgetRevPlans.Columns.Add("CreatedDate", GetType(Decimal)) '33
        dtBudgetRevPlans.Columns.Add("UpdatedDate", GetType(Decimal)) '34
        dtBudgetRevPlans.Columns.Add("CreatedBy", GetType(String)) '35
        dtBudgetRevPlans.Columns.Add("UpdatedBy", GetType(String)) '36
        dtBudgetRevPlans.Columns.Add("FundCenter_FundCenterCode", GetType(String)) '37
        dtBudgetRevPlans.Columns.Add("FundCenter_PlanYear", GetType(Integer)) '38
        dtBudgetRevPlans.Columns.Add("Product_ProductCode", GetType(String)) '39
        dtBudgetRevPlans.Columns.Add("Product_PlanYear", GetType(Integer)) '40
        dtBudgetRevPlans.Columns.Add("ProductItem_Id", GetType(Integer)) '41
        dsJob.Tables.Add(dtBudgetRevPlans)
        '******************************************************************************************
        'Datatable Attachment
        dtAttachment.Columns.Add("Id", GetType(Integer)) '1
        dtAttachment.Columns.Add("ItemNo", GetType(Integer)) '2
        dtAttachment.Columns.Add("Title", GetType(String)) '3
        dtAttachment.Columns.Add("FileName", GetType(String)) '4
        dtAttachment.Columns.Add("FileType", GetType(String)) '5
        dtAttachment.Columns.Add("FileSize", GetType(Integer)) '6
        dtAttachment.Columns.Add("MimeType", GetType(String)) '7
        dtAttachment.Columns.Add("CreatedDate", GetType(Date)) '8
        dtAttachment.Columns.Add("UpdatedDate", GetType(Date)) '9
        dtAttachment.Columns.Add("CreatedBy", GetType(String)) '10
        dtAttachment.Columns.Add("UpdatedBy", GetType(String)) '11
        dtAttachment.Columns.Add("BudgetDetail_Id", GetType(Integer)) '12
        dtAttachment.Columns.Add("delete_flag", GetType(Integer)) '13
        dsJob.Tables.Add(dtAttachment)
        '******************************************************************************************
        'Datable FundcenterRelease
        dtFundRelease.Columns.Add("Id", GetType(Integer)) '1
        dtFundRelease.Columns.Add("PlanYear", GetType(Integer)) '2 
        dtFundRelease.Columns.Add("ReleaseFlag", GetType(String)) '3
        dtFundRelease.Columns.Add("LastVersionFlag", GetType(String)) '4
        dtFundRelease.Columns.Add("Version", GetType(String)) '5
        dtFundRelease.Columns.Add("PrevVersion", GetType(String)) '6
        dtFundRelease.Columns.Add("ForceFlag", GetType(String)) '7
        dtFundRelease.Columns.Add("Remark", GetType(String)) '8
        dtFundRelease.Columns.Add("ReleaseReason", GetType(String)) '9
        dtFundRelease.Columns.Add("UnreleasableFlag", GetType(String)) '10
        dtFundRelease.Columns.Add("CreatedDate", GetType(Date)) '11
        dtFundRelease.Columns.Add("UpdatedDate", GetType(Date)) '12
        dtFundRelease.Columns.Add("CreatedBy", GetType(String)) '13
        dtFundRelease.Columns.Add("UpdatedBy", GetType(String)) '14
        dtFundRelease.Columns.Add("FundCenter_FundCenterCode", GetType(String)) '15
        dtFundRelease.Columns.Add("FundCenter_PlanYear", GetType(Integer)) '16
        dsJob.Tables.Add(dtFundRelease)
        '******************************************************************************************
    End Sub
    Private Function connectDB() As Boolean
        Try
            strConnString = ConfigurationManager.ConnectionStrings("WebBBConnectionString").ConnectionString
            DB_Connect = New SqlConnection(strConnString)
            DB_Connect.Open()
            OpenFlag = True
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            DB_Connect.Close()
        End Try
        Return OpenFlag
    End Function
    Private Function closeDB() As Boolean
        Try
            OpenFlag = False
            DB_Connect.Close()
            DB_Connect.Dispose()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
        Return OpenFlag
    End Function
    Private Sub GetJobList(Jobname As String)
        'get only avance with toVersion not 1-5 
        connectDB()
        If OpenFlag = True Then
            CheckAdvanceJob()
            Dim JLQuery As String = "SELECT * FROM JOBLIST JL WHERE JL.JOBNAME = @JOBNAME AND JL.JOBSTATUS = 'SUBMIT' ORDER BY JL.JOBID "

            Dim JLCommand As New SqlCommand(JLQuery, DB_Connect)
            JLCommand.Parameters.AddWithValue("@JOBNAME", Jobname)
            JLCommand.Transaction = JobTransaction
            Dim JLReader As SqlDataReader
            Try
                JLReader = JLCommand.ExecuteReader()
                If (JLReader.HasRows) Then
                    Do While JLReader.Read()
                        If CheckIsUpper5(JLReader.Item("ToVersion").ToString()) = True Then
                            'Get JobList
                            Dim temp_JobId As Integer = JLReader.Item("JobId")
                            Dim temp_JobName As String = JLReader.Item("JobName")
                            Dim temp_JobStatus As String = JLReader.Item("JobStatus")
                            Dim temp_SubmitBy As String = JLReader.Item("SubmitBy")
                            Dim temp_Remark As String = JLReader.Item("Remark")
                            Dim temp_FundCenterCode As String = JLReader.Item("FundCenterCode")
                            Dim temp_PlanYear As Integer = JLReader.Item("PlanYear")
                            Dim temp_FromVersion As String = JLReader.Item("FromVersion")
                            Dim temp_ToVersion As String = JLReader.Item("ToVersion")
                            Dim temp_CreateDate As Date = JLReader.Item("CreateDate")
                            Dim temp_CreateBy As String = JLReader.Item("CreateBy")
                            Dim temp_UpdateDate As Date = JLReader.Item("UpdateDate")
                            Dim temp_UpdateBy As String = JLReader.Item("UpdateBy")
                            dtJobList.Rows.Add(temp_JobId, temp_JobName, temp_JobStatus, temp_SubmitBy, temp_Remark, temp_FundCenterCode, temp_PlanYear, temp_FromVersion, temp_ToVersion, temp_CreateDate, temp_CreateBy, temp_UpdateDate, temp_UpdateBy)
                        End If
                    Loop
                End If
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End If
        closeDB()
    End Sub
    Private Sub CheckAdvanceJob()
        Try
            'if advance job fromVersion <5
            Dim JLCheck As String = "SELECT * FROM JOBLIST JL WHERE JL.JOBNAME = 'ADVANCE_COPY' AND JL.JOBSTATUS = 'SUBMIT' AND CONVERT(INT, FROMVERSION) < 5 ORDER BY JL.JOBID"
            If DB_Connect.State.Equals(ConnectionState.Closed) Then
                connectDB()
            End If
            'Store in TempTable
            Dim _dt As New DataTable("TempCheck")
            Using adapter As New SqlDataAdapter(JLCheck, DB_Connect)
                If adapter.SelectCommand.Connection.State <> ConnectionState.Open Then adapter.SelectCommand.Connection.Open()
                adapter.Fill(_dt)
            End Using
            'Check 
            For Each _dr As DataRow In _dt.Rows
                If CheckIsUpper5(_dr("FromVersion").ToString()) = False Then
                    UpdateJobListStatus(_dr("JobId"), "FAIL", "'Cannot do advance copy from " & _dr("FromVersion") & " to " & _dr("ToVersion") & "'")
                End If
            Next
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub
    Private Function CheckIsUpper5(ByVal GetValue As String) As Boolean
        Dim isUpper As Boolean = True
        Try
            If IsNumeric(GetValue) Then
                If Convert.ToInt32(GetValue) <= 5 Then
                    isUpper = False
                End If
            End If
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
        Return isUpper
    End Function
    Private Sub UpdateJobListStatus(ByVal _jobid As Integer, ByVal _status As String, ByVal _remark As String)
        Try
            Dim JLUpdate As String = " UPDATE JOBLIST SET JOBSTATUS = '" & _status & "', REMARK = " & _remark & ", UPDATEDATE = GETDATE(),UPDATEBY = 'BATCH_SYSTEM' WHERE JOBID = " & _jobid
            'update status to FAIL and Remark
            Transadap.UpdateCommand.Transaction = JobTransaction
            Transadap.UpdateCommand = New SqlCommand(JLUpdate, DB_Connect)
            Transadap.UpdateCommand.ExecuteNonQuery()

        Catch ex As Exception
            Console.WriteLine("Update job status {0}", ex.Message)
        End Try
    End Sub
    Private Sub ExecuteTransaction(ByVal _jobrow As DataRow)
        connectDB()

        ' Start a local transaction
        JobTransaction = DB_Connect.BeginTransaction("JobTransaction")

        ' Must assign both transaction object and connection 
        ' to Command object for a pending local transaction.
        Transcmd = DB_Connect.CreateCommand()
        Transcmd.Connection = DB_Connect
        Transcmd.Transaction = JobTransaction

        Try
            'Insert JobHistory with start time
            ModifiedJobHistory(_jobrow("JobId"), Now, Nothing)
            'Start copy data from BudgetDetail
            CopyBudgetDetail(_jobrow("JobName"), _jobrow("PlanYear"), _jobrow("FromVersion"), _jobrow("FundCenterCode"), _jobrow("PlanYear"))
            InsertBudgetDetail(dtBudgetDetail, _jobrow("ToVersion"))
            'Copy data from Budget12
            CopyBudget12(_jobrow("JobId"), _jobrow("FromVersion"))
            InsertBudget12(dtBudget12, _jobrow("ToVersion"))
            'Copy data from FundcenterRelease
            CopyFundRelease(dtFundRelease, _jobrow("FundCenterCode"))
            InsertFundRelease(dtFundRelease)
            'Copy data from BudgetRevPlans
            Dim _productcode, _productitemno, _budgetdetailid As Integer
            CopyBudgetRevPlans(_jobrow("FundCenterCode"), _productcode, _productitemno)
            InsertBudgetRevPlans(dtBudgetRevPlans)
            'Copy data from Attachments
            CopyAttachments(_budgetdetailid)
            InsertAttachments(dtAttachment)
            'Update JobHistory with end time
            ModifiedJobHistory(_jobrow("JobId"), Nothing, Now)

            ' Attempt to commit the transaction.
            ' Transcmd.ExecuteNonQuery()

            'Updated Time and status = done in JobList
            'UpdateJobListStatus(_jobrow("JobId"), "DONE", "NULL")

            ' Attempt to commit the transaction.
            JobTransaction.Commit()
            Console.WriteLine("Transaction Complted.")

        Catch ex As Exception
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
            Console.WriteLine("  Message: {0}", ex.Message)

            ' Attempt to roll back the transaction. 
            Try
                JobTransaction.Rollback("JobTransaction")

            Catch ex2 As Exception
                ' This catch block will handle any errors that may have occurred 
                ' on the server that would cause the rollback to fail, such as 
                ' a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                Console.WriteLine("  Message: {0}", ex2.Message)
            End Try
            'Finally
            '    JobTransaction.Dispose()
            '    Transcmd.Dispose()
            '    DB_Connect.Close()
        End Try
        closeDB()
    End Sub
    Private Sub ModifiedJobHistory(ByVal jobid As Integer, ByVal starttime As Date, ByVal endtime As Date)
        Dim JHInsertQuery, JHUpdateQuery As String
        Try
            If endtime = Nothing Then
                JHInsertQuery = "INSERT INTO JobHistory SELECT JOBID, Convert(datetime,' " & starttime & "'), NULL FROM JOBLIST WHERE JOBID = " & jobid
                Using Transcmd As New SqlCommand(JHInsertQuery, DB_Connect)
                    Transcmd.CommandText = JHInsertQuery
                    Transcmd.CommandType = CommandType.Text
                    Transcmd.Transaction = JobTransaction
                    Transcmd.ExecuteNonQuery()
                End Using
            Else
                JHUpdateQuery = "UPDATE JobHistory SET ENDTIME = Convert(datetime,' " & endtime & "') WHERE JOBID = " & jobid
                Using Transcmd As New SqlCommand(JHUpdateQuery, DB_Connect)
                    Transcmd.CommandText = JHUpdateQuery
                    Transcmd.CommandType = CommandType.Text
                    Transcmd.Transaction = JobTransaction
                    Transcmd.ExecuteNonQuery()
                End Using
            End If
        Catch ex As Exception
            Console.WriteLine("Update JobHistory {0}", ex.Message)
        End Try
    End Sub
    Private Sub CopyBudgetDetail(ByVal _jobname As String, ByVal _planyear As Int16, ByVal _version As String, ByVal _fundcode As String, ByVal _fundplanyear As Int16)
        Try
            Dim GetBDQuery As String = String.Empty
            If Not _jobname.StartsWith("ADVANCE") Then
                GetBDQuery = "SELECT * FROM BUDGETDETAILS WHERE PLANYEAR ='" & _planyear & "' AND VERSION ='" & _version & "' AND FUNDCENTER_FUNDCENTERCODE ='" & _fundcode & "' AND FUNDCENTER_PLANYEAR = '" & _fundplanyear & "' "
            Else
                GetBDQuery = "SELECT * FROM BUDGETDETAILS WHERE PLANYEAR ='" & _planyear & "' AND VERSION ='" & _version & "' "
            End If

            Using adapter As New SqlDataAdapter(GetBDQuery, DB_Connect)
                If adapter.SelectCommand.Connection.State <> ConnectionState.Open Then adapter.SelectCommand.Connection.Open()
                adapter.SelectCommand.Transaction = JobTransaction
                adapter.SelectCommand.CommandText = GetBDQuery
                adapter.Fill(dtBudgetDetail)
            End Using

            For Each dr As DataRow In dtBudgetDetail.Rows
                For i As Integer = 0 To dtBudgetDetail.Columns.Count - 1
                    Select Case dr(i).GetType()
                        Case GetType(String)
                            IIf(dr(i).ToString() = "" Or dr(i) Is Nothing Or String.IsNullOrEmpty(dr(i).ToString()), DBNull.Value, dr(i))
                        Case GetType(Decimal)
                            IIf(dr(i) >= 0, Convert.ToDecimal(dr(i)), DBNull.Value)
                        Case GetType(Short)
                            IIf(dr(i) >= 0, Convert.ToInt16(dr(i)), DBNull.Value)
                        Case GetType(Integer)
                            IIf(dr(i) >= 0, Convert.ToInt32(dr(i)), DBNull.Value)
                        Case GetType(Date)
                            Convert.ToDateTime(dr(i))
                        Case Else
                            dr(i) = DBNull.Value
                    End Select
                Next
            Next
            dtBudgetDetail.AcceptChanges()

        Catch ex As Exception
            Console.WriteLine("Copy BudgetDetail error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub InsertBudgetDetail(ByRef _dt As DataTable, ByVal newVersion As String)
        Dim InsertBDQuery As String
        Try
            For Each drBudgetDetail As DataRow In dtBudgetDetail.Rows
                Dim ud As Date
                If DBNull.Value.Equals(drBudgetDetail.Item("UpdatedDate")) Then
                    ud = CType(Now, Date)
                Else
                    If Not DBNull.Value.Equals(drBudgetDetail.Item("UpdatedDate")) Then
                        ud = CType(drBudgetDetail.Item("UpdatedDate"), Date)
                    End If
                End If

                InsertBDQuery = "INSERT INTO BudgetDetails([PlanYear],[BaseYear],[Version],[AssetNo],[AssetDescription],[AssetRowNo],[OldAssetNo]," & _
                                "[Element1],[Element2],[Element3],[Element4],[Element5],[Element6],[Element7],[Element8],[Element9],[Amount],[NewQuantity], " & _
                                "[ReplaceQuantity],[PricePerUnit],[Note],[Identification],[FormId],[FndPrgGenFlag],[FundedProgram],[TmpFundedProgram], " & _
                                "[FunctionalArea],[Actual1],[Actual2],[Actual3],[Actual4],[Actual5],[Actual6],[Actual7],[Actual8],[Actual9],[Actual10], " & _
                                "[Actual11],[Actual12],[Actual13],[Actual14],[Actual15],[Actual16],[Attachments_amount],[ProductItemNo],[CreatedDate], " & _
                                "[UpdatedDate],[CreatedBy],[UpdatedBy],[ActivityType_PlanYear],[ActivityType_ActivityTypeCode],[Activity_ActivityCode], " & _
                "[Activity_PlanYear],[Product_ProductCode],[Product_PlanYear],[FundCenter_FundCenterCode],[FundCenter_PlanYear], " & _
                "[Project_ProjectCode],[Project_PlanYear],[Project_FundCenterCode],[CmmtDetail_Id],[Fund_FundCode],[Fund_PlanYear], " & _
                "[Cmmt_CmmtCode],[Cmmt_PlanYear]) VALUES ("

                InsertBDQuery += Convert.ToInt16(drBudgetDetail.Item("PlanYear")) & ", "
                InsertBDQuery += Convert.ToInt16(drBudgetDetail.Item("BaseYear")) & ", "
                InsertBDQuery += "'" & newVersion & "' , "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("AssetNo")), "''", "'" & drBudgetDetail.Item("AssetNo") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("AssetDescription")), "''", "'" & drBudgetDetail.Item("AssetDescription") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("AssetRowNo")), 0, Convert.ToInt16(drBudgetDetail.Item("AssetRowNo"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("OldAssetNo")), "''", "'" & drBudgetDetail.Item("OldAssetNo") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element1")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element1"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element2")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element2"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element3")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element3"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element4")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element4"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element5")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element5"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element6")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element6"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element7")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element7"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element8")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element8"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Element9")), 0, Convert.ToDecimal(drBudgetDetail.Item("Element9"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Amount")), 0, Convert.ToDecimal(drBudgetDetail.Item("Amount"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("NewQuantity")), 0, Convert.ToDecimal(drBudgetDetail.Item("NewQuantity"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("ReplaceQuantity")), 0, Convert.ToDecimal(drBudgetDetail.Item("ReplaceQuantity"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("PricePerUnit")), 0, Convert.ToDecimal(drBudgetDetail.Item("PricePerUnit"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Note")), "''", "'" & drBudgetDetail.Item("Note") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Identification")), "''", "'" & drBudgetDetail.Item("Identification") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("FormId")), "''", "'" & drBudgetDetail.Item("FormId") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("FndPrgGenFlag")), "''", "'" & drBudgetDetail.Item("FndPrgGenFlag") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("FundedProgram")), "''", "'" & drBudgetDetail.Item("FundedProgram") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("TmpFundedProgram")), "''", "'" & drBudgetDetail.Item("TmpFundedProgram") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("FunctionalArea")), "''", "'" & drBudgetDetail.Item("FunctionalArea") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual1")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual1"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual2")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual2"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual3")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual3"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual4")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual4"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual5")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual5"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual6")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual6"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual7")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual7"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual8")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual8"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual9")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual9"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual10")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual10"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual11")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual11"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual12")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual12"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual13")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual13"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual14")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual14"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual15")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual15"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Actual16")), 0, Convert.ToDecimal(drBudgetDetail.Item("Actual16"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Attachments_amount")), 0, Convert.ToInt16(drBudgetDetail.Item("Attachments_amount"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("ProductItemNo")), 0, Convert.ToInt16(drBudgetDetail.Item("ProductItemNo"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("CreatedDate")), CType(Now, Date), "'" & CType(drBudgetDetail.Item("CreatedDate"), Date) & "'") & ", "
                InsertBDQuery += "'" & ud & "', "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("CreatedBy")), "''", "'" & drBudgetDetail.Item("CreatedBy") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("UpdatedBy")), "''", "'" & drBudgetDetail.Item("UpdatedBy") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("ActivityType_PlanYear")), 0, Convert.ToInt16(drBudgetDetail.Item("ActivityType_PlanYear"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("ActivityType_ActivityTypeCode")), "''", "'" & drBudgetDetail.Item("ActivityType_ActivityTypeCode") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Activity_ActivityCode")), "''", "'" & drBudgetDetail.Item("Activity_ActivityCode") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Activity_PlanYear")), 0, Convert.ToInt16(drBudgetDetail.Item("Activity_PlanYear"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Product_ProductCode")), "''", "'" & drBudgetDetail.Item("Product_ProductCode") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Product_PlanYear")), 0, Convert.ToInt16(drBudgetDetail.Item("Product_PlanYear"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("FundCenter_FundCenterCode")), "''", "'" & drBudgetDetail.Item("FundCenter_FundCenterCode") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("FundCenter_PlanYear")), 0, Convert.ToInt16(drBudgetDetail.Item("FundCenter_PlanYear"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Project_ProjectCode")), "''", "'" & drBudgetDetail.Item("Project_ProjectCode") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Project_PlanYear")), 0, Convert.ToInt16(drBudgetDetail.Item("Project_PlanYear"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Project_FundCenterCode")), "''", "'" & drBudgetDetail.Item("Project_FundCenterCode") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("CmmtDetail_Id")), 0, Convert.ToInt16(drBudgetDetail.Item("CmmtDetail_Id"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Fund_FundCode")), "''", "'" & drBudgetDetail.Item("Fund_FundCode") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Fund_PlanYear")), 0, Convert.ToInt16(drBudgetDetail.Item("Fund_PlanYear"))) & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Cmmt_CmmtCode")), "''", "'" & drBudgetDetail.Item("Cmmt_CmmtCode") & "'") & ", "
                InsertBDQuery += IIf(DBNull.Value.Equals(drBudgetDetail.Item("Cmmt_PlanYear")), 0, Convert.ToInt16(drBudgetDetail.Item("Cmmt_PlanYear")))
                InsertBDQuery += " ) "
                Transcmd.CommandText = InsertBDQuery
                Transcmd.Transaction = JobTransaction
                Transcmd.ExecuteNonQuery()
            Next
        Catch ex As Exception
            Console.WriteLine("Insert BudgetDetail error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub CopyBudget12(ByVal _jobid As Integer, ByVal _newversion As String)
        Try
            Dim selectB12Query As String = "SELECT * FROM BUDGET12 WHERE BudgetDetail_Id = " & _jobid
            Using adapter As New SqlDataAdapter(selectB12Query, DB_Connect)
                If adapter.SelectCommand.Connection.State <> ConnectionState.Open Then adapter.SelectCommand.Connection.Open()
                adapter.SelectCommand.Transaction = JobTransaction
                adapter.SelectCommand.CommandText = selectB12Query
                adapter.Fill(dtBudget12)
            End Using
        Catch ex As Exception
            Console.WriteLine("Copy Budget12 error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub InsertBudget12(ByVal _dt As DataTable, ByVal _newversion As String)
        Try
            Dim InsertB12Query As String
            For Each drBudget12 As DataRow In dtBudget12.Rows
                InsertB12Query = "INSERT INTO Budget12 ([PlanYear],[BaseYear],[Version],[FunctionalArea],[FundedProgram],[Amount1],[Amount2]," & _
                                 "[Amount3],[Amount4],[Amount5],[Amount6],[Amount7],[Amount8],[Amount9] ,[Amount10],[Amount11],[Amount12]," & _
                                 "[ForecastYear1],[ForecastYear2],[ForecastYear3],[ForecastYear4],[ProductItemNo],[CreatedDate],[UpdatedDate]," & _
                                 "[CreatedBy],[UpdatedBy],[FundCenter_FundCenterCode],[FundCenter_PlanYear],[Fund_FundCode],[Fund_PlanYear]," & _
                                 "[Cmmt_CmmtCode],[Cmmt_PlanYear],[CmmtDetail_Id],[ActivityType_PlanYear],[ActivityType_ActivityTypeCode]," & _
                                 "[Activity_ActivityCode],[Activity_PlanYear],[Product_ProductCode],[Product_PlanYear],[Project_ProjectCode]," & _
                                 "[Project_PlanYear],[Project_FundCenterCode],[BudgetDetail_Id]"
                InsertB12Query += ") VALUES ("
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("PlanYear")), 0, Convert.ToInt16(drBudget12.Item("PlanYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("BaseYear")), 0, Convert.ToInt16(drBudget12.Item("BaseYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Version")), " ''", "'" & drBudget12.Item("Version") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("FunctionalArea")), " ''", "'" & drBudget12.Item("FunctionalArea") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("FundedProgram")), " ''", "'" & drBudget12.Item("FundedProgram") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount1")), 0, Convert.ToDecimal(drBudget12.Item("Amount1"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount2")), 0, Convert.ToDecimal(drBudget12.Item("Amount2"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount3")), 0, Convert.ToDecimal(drBudget12.Item("Amount3"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount4")), 0, Convert.ToDecimal(drBudget12.Item("Amount4"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount5")), 0, Convert.ToDecimal(drBudget12.Item("Amount5"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount6")), 0, Convert.ToDecimal(drBudget12.Item("Amount6"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount7")), 0, Convert.ToDecimal(drBudget12.Item("Amount7"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount8")), 0, Convert.ToDecimal(drBudget12.Item("Amount8"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount9")), 0, Convert.ToDecimal(drBudget12.Item("Amount9"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount10")), 0, Convert.ToDecimal(drBudget12.Item("Amount10"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount11")), 0, Convert.ToDecimal(drBudget12.Item("Amount11"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Amount12")), 0, Convert.ToDecimal(drBudget12.Item("Amount12"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("ForecastYear1")), 0, Convert.ToDecimal(drBudget12.Item("ForecastYear1"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("ForecastYear2")), 0, Convert.ToDecimal(drBudget12.Item("ForecastYear2"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("ForecastYear3")), 0, Convert.ToDecimal(drBudget12.Item("ForecastYear3"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("ForecastYear4")), 0, Convert.ToDecimal(drBudget12.Item("ForecastYear4"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("ProductItemNo")), 0, Convert.ToInt16(drBudget12.Item("ProductItemNo"))) & ", "
                InsertB12Query += "'" & checkDateType(drBudget12.Item("CreatedDate")) & "', "
                InsertB12Query += "'" & checkDateType(drBudget12.Item("UpdatedDate")) & "', "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("CreatedBy")), " ''", "'" & drBudget12.Item("CreatedBy") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("UpdatedBy")), " ''", "'" & drBudget12.Item("UpdatedBy") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("FundCenter_FundCenterCode")), " ''", "'" & drBudget12.Item("FundCenter_FundCenterCode") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("FundCenter_PlanYear")), 0, Convert.ToInt16(drBudget12.Item("FundCenter_PlanYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Fund_FundCode")), " ''", "'" & drBudget12.Item("Fund_FundCode") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Fund_PlanYear")), 0, Convert.ToInt16(drBudget12.Item("Fund_PlanYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Cmmt_CmmtCode")), " ''", "'" & drBudget12.Item("Cmmt_CmmtCode") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Cmmt_PlanYear")), 0, Convert.ToInt16(drBudget12.Item("Cmmt_PlanYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("CmmtDetail_Id")), 0, Convert.ToInt32(drBudget12.Item("CmmtDetail_Id"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("ActivityType_PlanYear")), 0, Convert.ToInt16(drBudget12.Item("ActivityType_PlanYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("ActivityType_ActivityTypeCode")), " ''", "'" & drBudget12.Item("ActivityType_ActivityTypeCode") & "'") & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Activity_ActivityCode")), 0, Convert.ToInt32(drBudget12.Item("Activity_ActivityCode"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Activity_PlanYear")), 0, Convert.ToInt16(drBudget12.Item("Activity_PlanYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Product_ProductCode")), 0, Convert.ToInt32(drBudget12.Item("Product_ProductCode"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Product_PlanYear")), 0, Convert.ToInt16(drBudget12.Item("Product_PlanYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Project_ProjectCode")), 0, Convert.ToInt32(drBudget12.Item("Project_ProjectCode"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Project_PlanYear")), 0, Convert.ToInt16(drBudget12.Item("Project_PlanYear"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("Project_FundCenterCode")), 0, Convert.ToInt32(drBudget12.Item("Project_FundCenterCode"))) & ", "
                InsertB12Query += IIf(DBNull.Value.Equals(drBudget12.Item("BudgetDetail_Id")), 0, Convert.ToInt32(drBudget12.Item("BudgetDetail_Id"))) & ", "

                Transcmd.CommandText = InsertB12Query
                Transcmd.Transaction = JobTransaction
                Transcmd.ExecuteNonQuery()
            Next
        Catch ex As Exception
            Console.WriteLine("Insert Budget12 error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub CopyFundRelease(ByVal _dt As DataTable, ByVal _fundcenter As String)
        Try
            Dim selectFRQuery As String = "SELECT * FROM FundCenterReleases WHERE FundCenter_FundCenterCode = " & _fundcenter
            Using adapter As New SqlDataAdapter(selectFRQuery, DB_Connect)
                If adapter.SelectCommand.Connection.State <> ConnectionState.Open Then adapter.SelectCommand.Connection.Open()
                adapter.SelectCommand.Transaction = JobTransaction
                adapter.SelectCommand.CommandText = selectFRQuery
                adapter.Fill(dtFundRelease)
            End Using
        Catch ex As Exception
            Console.WriteLine("Copy FundCenterReleases error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub InsertFundRelease(ByVal _dt As DataTable)
        Try
            Dim InsertFundRelease As String
            For Each drFundRelease As DataRow In dtFundRelease.Rows
                InsertFundRelease = "INSERT INTO FundCenterReleases ([PlanYear],[ReleaseFlag],[LastVersionFlag],[Version],[PrevVersion],[ForceFlag],[Remark],[ReleaseReason],[UnreleasableFlag],[CreatedDate],[UpdatedDate],[CreatedBy],[UpdatedBy],[FundCenter_FundCenterCode],[FundCenter_PlanYear]"
                InsertFundRelease += ")VALUES("
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("PlanYear")), 0, Convert.ToInt16(drFundRelease.Item("PlanYear"))) & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("ReleaseFlag")), "''", " '" & Convert.ToString(drFundRelease.Item("ReleaseFlag"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("LastVersionFlag")), "''", " '" & Convert.ToString(drFundRelease.Item("LastVersionFlag"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("Version")), "''", " '" & Convert.ToString(drFundRelease.Item("Version"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("PrevVersion")), "''", " '" & Convert.ToString(drFundRelease.Item("PrevVersion"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("ForceFlag")), "''", " '" & Convert.ToString(drFundRelease.Item("ForceFlag"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("Remark")), "''", " '" & Convert.ToString(drFundRelease.Item("Remark"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("ReleaseReason")), "''", " '" & Convert.ToString(drFundRelease.Item("ReleaseReason"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("UnreleasableFlag")), "''", " '" & Convert.ToString(drFundRelease.Item("UnreleasableFlag"))) & "' " & ", "
                InsertFundRelease += " '" & checkDateType(drFundRelease.Item("CreatedDate")) & "', "
                InsertFundRelease += " '" & checkDateType(drFundRelease.Item("UpdatedDate")) & "', "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("CreatedBy")), "''", " '" & Convert.ToString(drFundRelease.Item("CreatedBy"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("UpdatedBy")), "''", " '" & Convert.ToString(drFundRelease.Item("UpdatedBy"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("FundCenter_FundCenterCode")), "''", " '" & Convert.ToString(drFundRelease.Item("FundCenter_FundCenterCode"))) & "' " & ", "
                InsertFundRelease += IIf(DBNull.Value.Equals(drFundRelease.Item("FundCenter_PlanYear")), 0, Convert.ToInt16(drFundRelease.Item("FundCenter_PlanYear"))) & ", "
                InsertFundRelease += ")"
            Next
        Catch ex As Exception
            Console.WriteLine("Insert FundCenterReleases error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub CopyAttachments(ByVal BD_Id As Integer)
        Try
            Dim selectATQuery As String = "SELECT * FROM Attachments WHERE BudgetDetail_Id = " & BD_Id
            Using adapter As New SqlDataAdapter(selectATQuery, DB_Connect)
                If adapter.SelectCommand.Connection.State <> ConnectionState.Open Then adapter.SelectCommand.Connection.Open()
                adapter.SelectCommand.Transaction = JobTransaction
                adapter.SelectCommand.CommandText = selectATQuery
                adapter.Fill(dtAttachment)
            End Using
        Catch ex As Exception
            Console.WriteLine("Copy FundCenterReleases error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub InsertAttachments(ByVal _dt As DataTable)
        Try
            Dim InsertATQuery As String
            For Each drAttachment As DataRow In dtAttachment.Rows
                InsertATQuery = "INSERT INTO Attachments([ItemNo],[Title],[FileName],[FileType],[FileSize],[MimeType]"
                InsertATQuery += " ,[CreatedDate],[UpdatedDate],[CreatedBy],[UpdatedBy],[BudgetDetail_Id],[delete_flag])"
                InsertATQuery += " VALUES ("
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("ItemNo")), 0, Convert.ToInt16(drAttachment.Item("ItemNo"))) & ", "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("Title")), "''", " '" & Convert.ToString(drAttachment.Item("Title"))) & "' " & ", "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("FileName")), "''", " '" & Convert.ToString(drAttachment.Item("FileName"))) & "' " & ", "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("FileType")), "''", " '" & Convert.ToString(drAttachment.Item("FileType"))) & "' " & ", "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("FileSize")), 0, Convert.ToInt16(drAttachment.Item("FileSize"))) & ", "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("MimeType")), "''", " '" & Convert.ToString(drAttachment.Item("MimeType"))) & "' " & ", "
                InsertATQuery += " '" & checkDateType(drAttachment.Item("CreatedDate")) & "', "
                InsertATQuery += " '" & checkDateType(drAttachment.Item("UpdatedDate")) & "', "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("CreatedBy")), "''", " '" & Convert.ToString(drAttachment.Item("CreatedBy"))) & "' " & ", "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("UpdatedBy")), "''", " '" & Convert.ToString(drAttachment.Item("UpdatedBy"))) & "' " & ", "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("BudgetDetail_Id")), 0, Convert.ToInt16(drAttachment.Item("BudgetDetail_Id"))) & ", "
                InsertATQuery += IIf(DBNull.Value.Equals(drAttachment.Item("delete_flag")), 0, Convert.ToInt16(drAttachment.Item("delete_flag"))) & ", "
                InsertATQuery += " ) "
            Next
        Catch ex As Exception
            Console.WriteLine("Insert Attachments error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub CopyBudgetRevPlans(ByVal FundCenterCode As String, ByVal ProductCode As Integer, ByVal ProductItem As Integer)
        Try
            Dim selectBRPQuery As String = "SELECT * FROM BudgetRevPlans WHERE FundCenter_FundCenterCode = " & FundCenterCode & " AND Product_ProductCode = " & ProductCode & " AND ProductItem_Id = " & ProductItem
            Using adapter As New SqlDataAdapter(selectBRPQuery, DB_Connect)
                If adapter.SelectCommand.Connection.State <> ConnectionState.Open Then adapter.SelectCommand.Connection.Open()
                adapter.SelectCommand.Transaction = JobTransaction
                adapter.SelectCommand.CommandText = selectBRPQuery
                adapter.Fill(dtBudgetRevPlans)
            End Using
        Catch ex As Exception
            Console.WriteLine("Copy BudgetRevPlans error: {0}", ex.Message)
        End Try
    End Sub
    Private Sub InsertBudgetRevPlans(ByVal _dt As DataTable)
        Try
            Dim InsertBRPQuery As String
            For Each drBudgetRevPlans As DataRow In dtBudgetRevPlans.Rows
                InsertBRPQuery = "INSERT INTO BudgetRevPlans ([PlanYear],[BaseYear],[Version],[ActualLastYear2],[ActualLastYear1],[ForecastLastYear1]"
                InsertBRPQuery += ",[ForecastCurYear],[LastYearMonth1],[LastYearMonth2],[LastYearMonth3],[LastYearMonth4],[LastYearMonth5],[LastYearMonth6]"
                InsertBRPQuery += ",[LastYearMonth7],[LastYearMonth8],[LastYearMonth9],[LastYearMonth10],[LastYearMonth11],[LastYearMonth12]"
                InsertBRPQuery += ",[CurYearMonth1],[CurYearMonth2],[CurYearMonth3],[CurYearMonth4],[CurYearMonth5],[CurYearMonth6]"
                InsertBRPQuery += ",[CurYearMonth7],[CurYearMonth8],[CurYearMonth9],[CurYearMonth10],[CurYearMonth11],[CurYearMonth12]"
                InsertBRPQuery += ",[CreatedDate],[UpdatedDate],[CreatedBy],[UpdatedBy],[FundCenter_FundCenterCode]"
                InsertBRPQuery += ",[FundCenter_PlanYear],[Product_ProductCode],[Product_PlanYear],[ProductItem_Id]"
                InsertBRPQuery += ") VALUES ("
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("PlanYear")), 0, Convert.ToInt16(drBudgetRevPlans.Item("PlanYear"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("BaseYear")), 0, Convert.ToInt16(drBudgetRevPlans.Item("BaseYear"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("Version")), "''", " '" & Convert.ToString(drBudgetRevPlans.Item("Version"))) & "' " & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("ActualLastYear2")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("ActualLastYear2"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("ActualLastYear1")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("ActualLastYear1"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("ForecastLastYear1")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("ForecastLastYear1"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("ForecastCurYear")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("ForecastCurYear"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth1")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth1"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth2")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth2"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth3")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth3"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth4")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth4"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth5")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth5"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth6")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth6"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth7")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth7"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth8")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth8"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth9")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth9"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth10")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth10"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth11")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth11"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("LastYearMonth12")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("LastYearMonth12"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth1")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth1"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth2")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth2"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth3")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth3"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth4")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth4"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth5")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth5"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth6")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth6"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth7")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth7"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth8")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth8"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth9")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth9"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth10")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth10"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth11")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth11"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CurYearMonth12")), 0, Convert.ToDecimal(drBudgetRevPlans.Item("CurYearMonth12"))) & ", "
                InsertBRPQuery += " '" & checkDateType(drBudgetRevPlans.Item("CreatedDate")) & "', "
                InsertBRPQuery += " '" & checkDateType(drBudgetRevPlans.Item("UpdatedDate")) & "', "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("CreatedBy")), "''", " '" & Convert.ToString(drBudgetRevPlans.Item("CreatedBy"))) & "' " & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("UpdatedBy")), "''", " '" & Convert.ToString(drBudgetRevPlans.Item("UpdatedBy"))) & "' " & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("FundCenter_FundCenterCode")), "''", " '" & Convert.ToString(drBudgetRevPlans.Item("FundCenter_FundCenterCode"))) & "' " & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("FundCenter_PlanYear")), 0, Convert.ToInt16(drBudgetRevPlans.Item("FundCenter_PlanYear"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("Product_ProductCode")), "''", " '" & Convert.ToString(drBudgetRevPlans.Item("Product_ProductCode"))) & "' " & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("Product_PlanYear")), 0, Convert.ToInt16(drBudgetRevPlans.Item("Product_PlanYear"))) & ", "
                InsertBRPQuery += IIf(DBNull.Value.Equals(drBudgetRevPlans.Item("ProductItem_Id")), 0, Convert.ToInt16(drBudgetRevPlans.Item("ProductItem_Id"))) & ", "
                InsertBRPQuery += " ) "
            Next
        Catch ex As Exception
            Console.WriteLine("Insert BudgetRevPlans error: {0}", ex.Message)
        End Try
    End Sub
    Private Function checkDateType(_d As String) As Date
        Dim ud As Date
        Try
            If DBNull.Value.Equals(_d) Then
                ud = CType(Now, Date)
            Else
                If Not DBNull.Value.Equals(_d) Then
                    ud = CType(_d, Date)
                End If
            End If
        Catch ex As Exception
            Console.WriteLine("Check Date error: {0}", ex.Message)
        End Try
        Return ud
    End Function
End Class
